const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, QueryTypes} = require('../../models/db')
// 采购汇总-商品
router.post('/pur/cghzskh', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    let buId = formData.buId?formData.buId:0;
    let goodCatCode = await tool.getGoodCode(ctx)
    let invName = formData.invName?formData.invName:'';
    let currentPage = body.currentPage?body.currentPage:1;
    let pageSize = body.pageSize?body.pageSize:200;
    let offset = (currentPage-1) * pageSize;

    let excel = body.excel
    let sqls = `select a.id, a.tenantId, billDate, a.billNo, billState, billType, a.transType,
                       billStateName,printNo,b.cbyName,b.createdAt,
                       transTypeName, buId, contactCatId, buName, totalAmount, disAmount, ysAmount, rpAmount, accId,
                       accAmount, arrears,
                       accId1, accAmount1,
                       b.remark,billStateSort,goodCatName,invName ,spec,unit,price ,a.remark subRemark ,
                       a.qtyReal qty,
                       a.amountReal amount ,a.unit1,a.calNum
                from v_main_info a inner join v_main b on a.tenantId=b.tenantId and a.iid=b.id
                where a.tenantId=${tenantId} and b.billType='PUR' and b.billState='accounted'
    `
    if(beginDate) {
        sqls += ` and b.billDate>='${beginDate}' `
    }
    if(beginDate) {
        sqls += ` and b.billDate<='${endDate}' `
    }
    if(buId) {
        sqls += ` and b.buId=${buId} `
    }
    if(invName) {
        sqls += ` and a.invName like '%${invName}%' `
    }
    if(goodCatCode) {
        sqls += ` and a.goodCatCode like '${goodCatCode}%' `
    }
    if(excel) {
        sqls = ` select buName,invName,goodCatName,spec,unit1,sum(qty*calNum)/spec qty,sum(amount) amount from (${sqls}) a group by buName,invName, goodCatName, spec, unit1`
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return tool.doExcelOut(ctx, list)
    }
    else {
        sqls = ` select buName,invName,goodCatName,spec,unit1,sum(qty*calNum)/spec qty,sum(amount) amount from (${sqls}) a group by buName,invName, goodCatName, spec, unit1`
        let totalModel = await db.query(`select count(*) total from (${sqls}) a `, {type: QueryTypes.SELECT, plain: true})
        sqls = ` select * from (${sqls}) a order by invName offset ${offset} rows fetch next ${pageSize} rows only;`
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return ctx.suc('', {
            total: totalModel?totalModel.total: 0,
            list: list
        })
    }
})





module.exports = router;